VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsDirSendFormC"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Option Explicit
Public CrystalConnectString As String
Dim cJMail As clsJMail
Public Sub SendFormC()
'******************************************************************************
'* Function  : SendFormC                                                      *
'* Copyright : Mindkey                                                        *
'* Author    : Maurice Milne                                                  *
'* Project   : Tax Directives System                                          *
'******************************************************************************
'* Description : function to retrieve then Tax Direcives received, format     *
'* them into valid PDF files and EMail them to the client.                    *
'******************************************************************************
'* Parameters :                                                               *
'******************************************************************************
'* Version 1.0 - 11/7/2000 M.Milne                                            *
'* Version 1.1 - 26/10/2000 M.Milne - Added Support Info to end of e-mail to  *
'*  client.  Upport message retrieved using SP dir_get_email_footnote         *
'******************************************************************************
'CHANGES                                                                      *
'******************************************************************************
'****       PERSON:     ANTHONY PATTON                                        *
'****       DATE:       19-08-2002                                            *
'****       PURPOSE:    KEYTAX WORKFLOW INTEGRATION                           *
'****************************************************************************

Dim adoConn As ADODB.Connection
Dim adoComm As ADODB.Command
Dim adoIntComm As ADODB.Command

Dim adoRs As ADODB.Recordset
Dim adoRsParam As ADODB.Recordset
Dim adorsEMail As ADODB.Recordset
Dim adorsIT88 As ADODB.Recordset
Dim adorsSupportMsg As ADODB.Recordset 'Added 26/10/2000 to retrieve Support Message for EMail
Dim adoRSInterface As ADODB.Recordset

Dim sSupportMsg As String 'Added 26/10/2000 to hold Support Message for EMail
Dim sPDFPath As String
Dim lngRecordsAffected As Integer

Dim sErrMsg As String
Dim sEMailBody As String
Dim sFileName As String
Dim sIT88_Ref As String
Dim sConnStr As String
Dim sStage As String
Dim objReg As clsRegistry
Dim dTotalTax As Double
Dim iCounter As Integer
Dim adorsDeclineRsn As ADODB.Recordset 'Added 20/08/2000 to retrieve Decline Reasons for decline
Dim bFirstReason As Boolean 'Added 20/08/2000 to retrieve Decline Reasons for decline
Dim sfrequency As String
Dim sFormType As String
Dim objConn As clsConnect

'AP VARS
Dim rsDIR_INTERFACE_INDEX As ADODB.Recordset
Dim strSQL As String
Dim arrList() As String
Dim rsHolder As ADODB.Recordset
Dim lListCount As Long
Dim bOK As Integer
Dim sEmailWStnEmail As String
Dim sSendEmailToClient As String
Dim rsParams As ADODB.Recordset
Dim cmdKT As ADODB.Command

On Error GoTo Send_FormC_Error

'Check to see if this applications isn't already running. If so, exit function
Call IsAppRunning
If IsAppRunning = True Then
    Exit Sub
End If

Set rsParams = New ADODB.Recordset

'Set objReg = New clsRegistry
'sConnStr = Encrypt(objReg.AdoConnectString, "richardknowles")
'sConnStr = "Provider=SQLOLEDB.1;Data Source=lzg1504_01;User ID=sa;Password=mindkey;Initial Catalog=keytax_IS"
'sConnStr = "Provider=SQLOLEDB.1;Data Source=blueprnt-DEV2;User ID=sa; Password=blueprnt;Initial Catalog=keytax"
'Fikile(20110317): Take it back
'sConnStr = "Provider=SQLOLEDB.1;Data Source=SQL02;User ID=keytax; Password=m1ndk3y;Initial Catalog=keytax"
'Fikile(20110317)
sConnStr = "Provider=SQLOLEDB;Data Source=163.202.7.96\I2,1433;User ID=taxadmin; Password=taxadm1n;Initial Catalog=keytax"
'sConnStr = "Provider=SQLOLEDB;Data Source=163.202.7.96\I2,1433;User ID=taxadmin; Password=taxadm1n;Initial Catalog=keytax"
'sConnStr = "Provider=SQLOLEDB.1;Data Source=blueprnt_dev;User ID=keytax;Password=blueprnt;Initial Catalog=keytax"
'sConnStr = "Provider=SQLOLEDB.1;Data Source=blueprnt_sql;User ID=keytax;Password=mindkey;Initial Catalog=keytax"
'sConnStr = "Provider=SQLOLEDB;Driver={SQL Server};Server=SQLV02\I2;Initial Catalog=keytax;User ID=keytax; Password=k3ytax"

sStage = ""
'create connection to SQL Server
Set objConn = New clsConnect
Set adoConn = New ADODB.Connection
With adoConn
    .CursorLocation = adUseClient
    .IsolationLevel = adXactBrowse
    .ConnectionString = sConnStr
    .Open
End With

Set adoRs = New ADODB.Recordset
With adoRs
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
End With

Set adoComm = New ADODB.Command
With adoComm
    .CommandText = "DIR_directives_hardcopy_vw"
    .CommandType = adCmdTable
    Set .ActiveConnection = adoConn
End With
adoRs.Open "Dir_directives_hardcopy_vw", adoConn 'retrieve data

'AP get the email workstations address from the params table
rsParams.Open "dir_get_param_values 'EMAIL_WSTN_ADDR'", adoConn
If rsParams.BOF = True And rsParams.EOF = True Then
    MsgBox "the EMAIL_WSTN_ADDR parameter has not been set up in DIR_PARAMETERS"
    Exit Sub
Else
    sEmailWStnEmail = rsParams!VAR_VALUE
End If
rsParams.Close

'AP get the param to send the second emai Y/N from dir_paramaters
rsParams.Open "dir_get_param_values 'EMAIL_SEND_YN'", adoConn
If rsParams.BOF = True And rsParams.EOF = True Then
    MsgBox "the EMAIL_SEND_YN parameter has not been set up in DIR_PARAMETERS"
    Exit Sub
Else
    sSendEmailToClient = rsParams!VAR_VALUE
End If

sStage = "Getting Parameters"
'Create Recordset to access all tax directives pending despatch to client
Set adoRsParam = New ADODB.Recordset
adoRsParam.Open "Dir_get_param_values 'EMAIL_HOME'", adoConn 'retrieve data
If adoRsParam.EOF Then 'no path defined - report error
  adoRsParam.Close
  SendErrorEmail adoRs.Fields("req_seq_num"), "Unable to access PDF path location - missing EMAIL_HOME parameter"
  'adoConn.Execute "dir_insert_error '" & Date & "','Unable to access PDF path location - missing EMAIL_HOME parameter','clsDirSendFormC',0"
  Set adoRsParam = Nothing
  Exit Sub
End If
'Shane: Only for the purpose of testing on LZG
'sPDFPath = adoRs.Fields("var_value").Value
sPDFPath = "C:\"
If Dir(sPDFPath & "*.pdf") <> "" Then
    Kill sPDFPath & "*.pdf"
End If

'*******************************************************************
'* Added 26/10/2000 - Access Support Clause to add to end of EMail *
'*******************************************************************
sStage = "Getting Email Footer"
Set adorsSupportMsg = New ADODB.Recordset
adorsSupportMsg.Open "dir_get_email_footnote", adoConn 'retrieve data
'no support message then ignore
If Not adorsSupportMsg.EOF Then
  sSupportMsg = adorsSupportMsg(0)
End If
adorsSupportMsg.Close
Set adorsSupportMsg = Nothing

Form2.Show

sStage = "Getting Directives Details"

Set adorsEMail = New ADODB.Recordset
Set adorsIT88 = New ADODB.Recordset
iCounter = 0
    

'Step through each retrieved response and process
'*******************************************************************
'BUILD THE EMAIL BODY AND ATTACHMENT
'*******************************************************************
While Not adoRs.EOF
    sStage = "Getting EMail Body"
    'Tina: for IRP3A's for Liberty - 2003-08-22
    If adoRs.Fields("scod_form_type") = "IRP3A" Then
        adorsEMail.Open "dir_get_email_body_irp3a " & adoRs.Fields("req_seq_num").Value, adoConn 'get Email body for Response
    Else
        adorsEMail.Open "dir_get_email_body " & adoRs.Fields("req_seq_num").Value, adoConn 'get Email body for Response
    End If
    sFileName = ""
    If Not adorsEMail.EOF Then
      sStage = "Checking EMail Body"
      If adorsEMail.Fields("create_pdf_yn") = "Y" Then 'process successful Request
        crFormC.RecordSelectionFormula = " {DIR_directives_hardcopy_vw.req_seq_num} = " & adoRs.Fields("req_seq_num").Value
        sStage = "Clearing Old Body " & adoRs.Fields("req_seq_num")
        crFormC.DiscardSavedData 'clear out old data
        crFormC.Database.AddADOCommand sConnStr, adoComm
        crFormC.Database.Verify
        sStage = "Getting Report Body " & adoRs.Fields("req_seq_num")
        crFormC.ReadRecords 'get data
        
        'Sort Fund Address and move Postal Code to last free address line
        'Tina: for IRP3A's... 26/08/2003
        If IsNull(adoRs.Fields("addr_l2")) = True Then
          crFormC.FormulaFields(19).Text = Chr(34) & adoRs.Fields("addr_l2") & Chr(34)
        Else
            crFormC.FormulaFields(19).Text = Chr(34) & Replace(Replace(adoRs.Fields("addr_l2"), "'", "''"), "", """") & Chr(34)
        End If
        If IsNull(adoRs.Fields("addr_l3")) = True Then
          crFormC.FormulaFields(20).Text = Chr(34) & adoRs.Fields("addr_l3") & Chr(34)
        Else
          crFormC.FormulaFields(20).Text = Chr(34) & Replace(Replace(adoRs.Fields("addr_l3"), "'", "''"), "", """") & Chr(34)
        End If
        If IsNull(adoRs.Fields("addr_l4")) = True Then
          crFormC.FormulaFields(21).Text = Chr(34) & adoRs.Fields("addr_l4") & Chr(34)
        Else
          crFormC.FormulaFields(21).Text = Chr(34) & Replace(Replace(adoRs.Fields("addr_l4"), "'", "''"), """", """""") & Chr(34)
        End If
        
        crFormC.FormulaFields(22).Text = Chr(34) & adoRs.Fields("Postal_Code") & Chr(34)
        If crFormC.FormulaFields(21).Text = Chr(34) & Chr(34) Then
          crFormC.FormulaFields(21).Text = crFormC.FormulaFields(22).Text
          crFormC.FormulaFields(22).Text = ""
          If crFormC.FormulaFields(20).Text = Chr(34) & Chr(34) Then
            crFormC.FormulaFields(20).Text = crFormC.FormulaFields(21).Text
            crFormC.FormulaFields(21).Text = ""
            If crFormC.FormulaFields(19).Text = Chr(34) & Chr(34) Then
              crFormC.FormulaFields(19).Text = crFormC.FormulaFields(20).Text
              crFormC.FormulaFields(20).Text = ""
            End If
          End If
        End If
        
        'Sort address and move Postal Code to last free address line
        crFormC.FormulaFields(15).Text = Chr(34) & Replace(Replace(adoRs.Fields("home_addr_l2"), "'", "''"), """", """""") & Chr(34)
        If IsNull(adoRs.Fields("home_addr_l3")) = True Then
          crFormC.FormulaFields(16).Text = Chr(34) & adoRs.Fields("home_addr_l3") & Chr(34)
        Else
          crFormC.FormulaFields(16).Text = Chr(34) & Replace(Replace(adoRs.Fields("home_addr_l3"), "'", "''"), """", """""") & Chr(34)
        End If
        If IsNull(adoRs.Fields("home_addr_l4")) = True Then
          crFormC.FormulaFields(17).Text = Chr(34) & adoRs.Fields("home_addr_l4") & Chr(34)
        Else
          crFormC.FormulaFields(17).Text = Chr(34) & Replace(Replace(adoRs.Fields("home_addr_l4"), "'", "''"), """", """""") & Chr(34)
        End If
        crFormC.FormulaFields(18).Text = Chr(34) & adoRs.Fields("home_Postal_Code") & Chr(34)
        If crFormC.FormulaFields(17).Text = Chr(34) & Chr(34) Then
          crFormC.FormulaFields(17).Text = crFormC.FormulaFields(18).Text
          crFormC.FormulaFields(18).Text = ""
          If crFormC.FormulaFields(16).Text = Chr(34) & Chr(34) Then
            crFormC.FormulaFields(16).Text = crFormC.FormulaFields(17).Text
            crFormC.FormulaFields(17).Text = ""
            If crFormC.FormulaFields(15).Text = Chr(34) & Chr(34) Then
              crFormC.FormulaFields(15).Text = crFormC.FormulaFields(16).Text
              crFormC.FormulaFields(16).Text = ""
            End If
          End If
        End If
        
        'Display only relevant text based on form type
        If adoRs.Fields("scod_form_type") = "FORM_AD" Then
          crFormC.Section8.Suppress = False
          crFormC.Section9.Suppress = True
          crFormC.Section10.Suppress = True
        ElseIf adoRs.Fields("scod_form_type") = "FORM_B" Then
          crFormC.Section8.Suppress = False
          crFormC.Section9.Suppress = True
          crFormC.Section10.Suppress = True
        ElseIf adoRs.Fields("scod_form_type") = "FORM_C" Then
          crFormC.Section8.Suppress = False
          crFormC.Section9.Suppress = True
          crFormC.Section10.Suppress = True
        ElseIf adoRs.Fields("scod_form_type") = "FORM_E" Then
          crFormC.Section8.Suppress = False
          crFormC.Section9.Suppress = True
          crFormC.Section10.Suppress = True
        ElseIf adoRs.Fields("scod_form_type") = "IRP3A" Then
          crFormC.Section8.Suppress = False
          crFormC.Section9.Suppress = True
          crFormC.Section10.Suppress = True
        ElseIf adoRs.Fields("scod_form_type") = "IRP3B" Then
          crFormC.Section8.Suppress = True
          crFormC.Section9.Suppress = False
          crFormC.Section10.Suppress = True
        ElseIf adoRs.Fields("scod_form_type") = "IRP3C" Then
          crFormC.Section8.Suppress = True
          crFormC.Section9.Suppress = True
          crFormC.Section10.Suppress = False
        End If
        
        'Display either fund details or employer details
        If adoRs.Fields("scod_form_type") = "FORM_AD" Or adoRs.Fields("scod_form_type") = "FORM_B" Or adoRs.Fields("scod_form_type") = "FORM_C" Or adoRs.Fields("scod_form_type") = "FORM_E" Then
          crFormC.Section5.Suppress = True
          crFormC.Section6.Suppress = False
          crFormC.Section11.Suppress = False
        ElseIf adoRs.Fields("scod_form_type") = "IRP3A" Or adoRs.Fields("scod_form_type") = "IRP3B" Or adoRs.Fields("scod_form_type") = "IRP3C" Then
          crFormC.Section5.Suppress = False
          crFormC.Section6.Suppress = True
          crFormC.Section11.Suppress = True
        End If
        
        crFormC.ExportOptions.DiskFileName = sPDFPath & adoRs.Fields("Directive_number") & ".PDF"
        crFormC.ExportOptions.FormatType = crEFTPortableDocFormat '(PDF)
        crFormC.ExportOptions.DestinationType = crEDTDiskFile
        sStage = "Checking for IT88 " & adoRs.Fields("req_seq_num")
        'sIT88_Ref = ""
        sIT88_Ref = adoRs.Fields("it88_ref_no")
        If Not IsNull(sIT88_Ref) Then
          sIT88_Ref = Trim(sIT88_Ref)
        End If
        If sIT88_Ref = "" Then 'no IT88 Form required
          sStage = "Hiding IT88 notification on directive page"
          crFormC.FormulaFields.Item(2).Text = ""
          sStage = "Hiding IT88 Page"
          crFormC.Section13.Suppress = True
        Else
          sStage = "Showing IT88 Page"
          crFormC.Section13.Suppress = False 'add IT88 onto Form C
          sStage = "Getting IT88 Details " & adoRs.Fields("req_seq_num")
          adorsIT88.Open "Dir_get_it88_detail " & adoRs.Fields("req_seq_num"), adoConn 'retrieve data
          If adorsIT88.EOF Then 'if IT88 details cannot be retrieved then report error
              SendErrorEmail adoRs.Fields("req_seq_num"), "Unable to access IT88 details for REQ_SEQ_NUM-" & adoRs.Fields("req_seq_num")
            'adoConn.Execute "dir_insert_error '" & Date & "','Unable to access IT88 details for REQ_SEQ_NUM-" & adoRs.Fields("req_seq_num") & "','clsDirSendFormC',0"
            adorsIT88.Close
            Set adorsIT88 = Nothing
            Set adoConn = Nothing
            Exit Sub
          End If
          sStage = "Inserting IT88 Details " & adoRs.Fields("req_seq_num")
          'Reset Tax Calculation table to default values
          crFormC.FormulaFields.Item(4).Text = Chr(34) & "N/A" & Chr(34)
          crFormC.FormulaFields.Item(5).Text = Chr(34) & "0.00" & Chr(34)
          crFormC.FormulaFields.Item(6).Text = Chr(34) & "0.00" & Chr(34)
          crFormC.FormulaFields.Item(7).Text = Chr(34) & "N/A" & Chr(34)
          crFormC.FormulaFields.Item(8).Text = Chr(34) & "N/A" & Chr(34)
          crFormC.FormulaFields.Item(10).Text = Chr(34) & "0.00" & Chr(34)
          crFormC.FormulaFields.Item(11).Text = Chr(34) & "0.00" & Chr(34)
          crFormC.FormulaFields.Item(12).Text = Chr(34) & "0.00" & Chr(34)
          crFormC.FormulaFields.Item(13).Text = Chr(34) & "0.00" & Chr(34)
          dTotalTax = 0
          'Move data from Recordset into Formulas on Report
          crFormC.FormulaFields.Item(2).Text = Chr(34) & adorsIT88.Fields("it88_ref_no").Value & Chr(34)
          crFormC.FormulaFields.Item(3).Text = Chr(34) & adorsIT88.Fields("stop_order_rsn").Value & Chr(34)
          If adorsIT88.Fields("vat_reg_no").Value <> "" Then crFormC.FormulaFields.Item(4).Text = Chr(34) & adorsIT88.Fields("vat_reg_no").Value & Chr(34)
           If adorsIT88.Fields("vat_amount").Value <> "" Then crFormC.FormulaFields.Item(5).Text = Chr(34) & Format(adorsIT88.Fields("vat_amount").Value, "#,##0.00") & Chr(34)
          dTotalTax = dTotalTax + IIf(IsNull(adorsIT88.Fields("vat_amount").Value), 0, adorsIT88.Fields("vat_amount").Value)
          If Not IsNull(adorsIT88.Fields("paye_amt").Value) Then crFormC.FormulaFields.Item(6).Text = Chr(34) & Format(adorsIT88.Fields("paye_amt").Value, "#,##0.00") & Chr(34)
          dTotalTax = dTotalTax + IIf(IsNull(adorsIT88.Fields("paye_amt").Value), 0, adorsIT88.Fields("paye_amt").Value)
          If Not IsNull(adorsIT88.Fields("per1").Value) Then crFormC.FormulaFields.Item(7).Text = Chr(34) & adorsIT88.Fields("per1").Value & Chr(34)
          If Not IsNull(adorsIT88.Fields("per2").Value) Then crFormC.FormulaFields.Item(8).Text = Chr(34) & adorsIT88.Fields("per2").Value & Chr(34)
          If Not IsNull(adorsIT88.Fields("per3").Value) Then crFormC.FormulaFields.Item(9).Text = Chr(34) & adorsIT88.Fields("per3").Value & Chr(34)
          If Not IsNull(adorsIT88.Fields("amt1").Value) Then crFormC.FormulaFields.Item(10).Text = Chr(34) & Format(adorsIT88.Fields("amt1").Value, "#,##0.00") & Chr(34)
          dTotalTax = dTotalTax + IIf(IsNull(adorsIT88.Fields("amt1").Value), 0, adorsIT88.Fields("amt1").Value)
          If Not IsNull(adorsIT88.Fields("amt2").Value) Then crFormC.FormulaFields.Item(11).Text = Chr(34) & Format(adorsIT88.Fields("amt2").Value, "#,##0.00") & Chr(34)
          dTotalTax = dTotalTax + IIf(IsNull(adorsIT88.Fields("amt2").Value), 0, adorsIT88.Fields("amt2").Value)
          If Not IsNull(adorsIT88.Fields("amt3").Value) Then crFormC.FormulaFields.Item(12).Text = Chr(34) & Format(adorsIT88.Fields("amt3").Value, "#,##0.00") & Chr(34)
          dTotalTax = dTotalTax + IIf(IsNull(adorsIT88.Fields("amt3").Value), 0, adorsIT88.Fields("amt3").Value)
          If Not IsNull(adorsIT88.Fields("assessed_tax_amt").Value) Then crFormC.FormulaFields.Item(13).Text = Chr(34) & Format(adorsIT88.Fields("assessed_tax_amt").Value, "#,##0.00") & Chr(34)
          dTotalTax = dTotalTax + IIf(IsNull(adorsIT88.Fields("assessed_tax_amt").Value), 0, adorsIT88.Fields("assessed_tax_amt").Value)
          crFormC.FormulaFields.Item(14).Text = Chr(34) & Format(dTotalTax, "#,##0.00") & Chr(34)
          adorsIT88.Close
        End If
        sStage = "Exporting FormC Data " & adoRs.Fields("req_seq_num")
        crFormC.Export (False) 'data to disk
        sFileName = sPDFPath & adoRs.Fields("directive_number") & ".pdf" 'attach PDF file
         'sFileName = "C:\Program Files\Adobe\Acrobat 4.0\Reader\ReadMe.pdf"
      End If
      
      sStage = "Creating EMail"
        'Build EMail Body
        sEMailBody = "SARS Application No : " & vbTab & adorsEMail.Fields("SARS_appl_id") & vbCrLf
        sEMailBody = sEMailBody & "KeyTax request number : " & vbTab & adorsEMail.Fields("req_seq_num") & vbCrLf
        'Tina: for IRP3A's... 26/08/2003
        If adoRs.Fields("scod_form_type") <> "IRP3A" Then
            sEMailBody = sEMailBody & "Fund : " & vbTab & vbTab & vbTab & vbTab & adorsEMail.Fields("Fund") & vbCrLf
            sEMailBody = sEMailBody & "Contract number : " & vbTab & vbTab & adorsEMail.Fields("Contract_number") & vbCrLf
        End If
        sEMailBody = sEMailBody & "Tax Payer : " & vbTab & vbTab & vbTab & adorsEMail.Fields("Taxpayer") & vbCrLf
        sEMailBody = sEMailBody & "Directive number : " & vbTab & vbTab & adorsEMail.Fields("directive_number") & vbCrLf
        sEMailBody = sEMailBody & "Requested date : " & vbTab & vbTab & Format(adorsEMail.Fields("request_date"), "DD/MM/YYYY") & vbCrLf
        sEMailBody = sEMailBody & "Requested by : " & vbTab & vbTab & vbTab & adorsEMail.Fields("requested_by") & vbCrLf
        sEMailBody = sEMailBody & "Request status : " & vbTab & vbTab & adorsEMail.Fields("request_status") & vbCrLf & vbCrLf
        If (UCase(adorsEMail.Fields("request_status")) = "APPLICATION DECLINED") Or _
           (UCase(adorsEMail.Fields("request_status")) = "DIRECTIVE DECLINED") Then
            'Fetch all the reasons
            sStage = "Getting Decline Reasons"
            Set adorsDeclineRsn = New ADODB.Recordset
            bFirstReason = True
            adorsDeclineRsn.Open "select * from dir_decline_reasons where req_seq_num =" & adorsEMail.Fields("req_seq_num"), adoConn 'retrieve data
            'no support message then ignore
            While Not adorsDeclineRsn.EOF
                If bFirstReason Then
                    If adorsDeclineRsn.Fields("decline_reason_code") = "05807" Then
                        sEMailBody = sEMailBody & "Decline Reason(s) : " & vbTab & vbTab & adorsDeclineRsn.Fields("decline_reason_code") & ": Refer to your local Receiver of Revenue office." & vbCrLf
                    Else
                        sEMailBody = sEMailBody & "Decline Reason(s) : " & vbTab & vbTab & adorsDeclineRsn.Fields("decline_reason_code") & ": " & adorsDeclineRsn.Fields("decline_reason_text") & vbCrLf
                    End If
                    bFirstReason = False
                Else
                    If adorsDeclineRsn.Fields("decline_reason_code") = "05807" Then
                        sEMailBody = sEMailBody & vbTab & vbTab & vbTab & vbTab & adorsDeclineRsn.Fields("decline_reason_code") & ": Refer to your local Receiver of Revenue office." & vbCrLf
                    Else
                        sEMailBody = sEMailBody & vbTab & vbTab & vbTab & vbTab & adorsDeclineRsn.Fields("decline_reason_code") & ": " & adorsDeclineRsn.Fields("decline_reason_text") & vbCrLf
                    End If
                End If
                adorsDeclineRsn.MoveNext
            Wend
            adorsDeclineRsn.Close
            Set adorsDeclineRsn = Nothing
        End If
        sEMailBody = sEMailBody & vbCrLf & sSupportMsg 'added 26/10/2000  - add support msg to end of e-mail
        sStage = "Submitting EMail " & adoRs.Fields("EMail_address")
    Else 'if EMail body missing
          SendErrorEmail adoRs.Fields("req_seq_num"), "No EMail Body Located for ID: " & adoRs.Fields("req_seq_num")
    End If
    
    '************************************************************************
    '****       AUTHOR:     ANTHONY PATTON
    '****       DATE:       19-08-2002
    '****       FUNCTION:   KeyTax Workflow Integration.
    '****       COMMENTS:   This code will check if the current response
    '****                   is for an automated request, if it is a second
    '****                   email will be sent to an Email Workstation address
    '****                   for autologger commital to the workflow.
    '************************************************************************
    Set cJMail = Nothing 'clear the old msg out
    Set cJMail = New clsJMail 'set up for new msg
    'look up in the DIR_INTERFACE_INDEX (is this a response to an auto request)
    strSQL = "select count(*) from DIR_INTERFACE_INDEX where REQ_SEQ_NUM=" & adoRs.Fields("req_seq_num")
    Set rsDIR_INTERFACE_INDEX = adoConn.Execute(strSQL)
    If rsDIR_INTERFACE_INDEX.Fields(0).Value > 0 Then
        Set rsHolder = New ADODB.Recordset
        'the current response has an auto request record
        rsHolder.Open "dir_get_param_values 'INCLUDE_IN_EMAIL'", adoConn
        If rsHolder.BOF = True And rsHolder.EOF = True Then
            MsgBox "The INCLUDE_IN_EMAIL parameter has not been set up in the dir_parameters table."
        End If
        
        arrList = Split(rsHolder!VAR_VALUE, ",")
        bOK = 0
        
        If UBound(arrList) > 0 Then
            For lListCount = 0 To UBound(arrList) - 1
                strSQL = "select scod_dir_req_rsn from dir_request where req_seq_num = " & adoRs.Fields("req_seq_num")
                Set rsHolder = adoConn.Execute(strSQL)
                If arrList(lListCount) = rsHolder.Fields("scod_dir_req_rsn") Then
                    bOK = 1
                    Exit For
                End If
            Next
        Else
            MsgBox "the INCLUDE_IN_EMAIL parameter has not been set up in DIR_PARAMETERS"
            Exit Sub
        End If
        
'****************************************************************************************
'INTERFACE REQUEST BUT YOU CAN CHANGE PARAM IN PARAMS TABLE SO THAT USER STILL GETS EMAIL
'****************************************************************************************

        If rsDIR_INTERFACE_INDEX.EOF = False And rsDIR_INTERFACE_INDEX.BOF = False Then
            If sSendEmailToClient = "Y" And bOK = 1 Then
                'if the mail to the client is yes and the request is auto
                'Send Mail if the param says so, and its an auto response
                Set cJMail = New clsJMail
                
                cJMail.EMailServer = "trend1.liberty.co.za"
                cJMail.MsgFrom = "info@keytax.co.za"
                cJMail.MSGFromName = "KeyTax: Tax Directives"
                cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
                cJMail.MSGBody = sEMailBody
                cJMail.AddRecipient adoRs.Fields("EMail_address"), adorsEMail.Fields("requested_by")
                strEmailAddress = adoRs.Fields("EMail_address") & " " & adorsEMail.Fields("requested_by")
                If sFileName <> "" Then
                  cJMail.AddAttachment sFileName
                End If
                    
                If Not cJMail.SendMSG Then
                    MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                    'adoConn.Execute "dir_insert_error '" & Date & "','" & "Error Sending Mail" & "','clsSendEMail',0"
                  Set adoConn = Nothing
                  Exit Sub
                End If
                
                'its an auto request and send to client is no but its in the list
                'so send it to email work station
                 Set cJMail = New clsJMail
                 
                 cJMail.EMailServer = "trend1.liberty.co.za"
                 cJMail.MsgFrom = adoRs.Fields("req_seq_num") & "@keytax.co.za"
                 cJMail.MSGFromName = "KeyTax: Tax Directives"
                 cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
                 cJMail.MSGBody = sEMailBody
                 cJMail.AddRecipient sEmailWStnEmail, "Workflow"
                 strEmailAddress = adoRs.Fields("EMail_address") & " " & adorsEMail.Fields("requested_by")
                 If sFileName <> "" Then
                   cJMail.AddAttachment sFileName
                 End If
                     
                 If Not cJMail.SendMSG Then
                     MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                     'adoConn.Execute "dir_insert_error '" & Date & "','" & "Error Sending Mail" & "','clsSendEMail',0"
                   Set adoConn = Nothing
                   Exit Sub
                 End If
'****************************************************************************************
'INTERFACE REQUEST - SEND TO WORKFLOW
'****************************************************************************************
            Else
                If bOK = 1 Then
                   'its an auto request and send to client is no but its in the list
                   'so send it to email work station
                    Set cJMail = New clsJMail
                    
                    cJMail.EMailServer = "trend1.liberty.co.za"
                    cJMail.MsgFrom = adoRs.Fields("req_seq_num") & "@keytax.co.za"
                    cJMail.MSGFromName = "KeyTax: Tax Directives"
                    cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
                    cJMail.MSGBody = sEMailBody
                    cJMail.AddRecipient sEmailWStnEmail, "Workflow"
                    strEmailAddress = adoRs.Fields("EMail_address") & " " & adorsEMail.Fields("requested_by")
                    
                    If sFileName <> "" Then
                      cJMail.AddAttachment sFileName
                    End If
                        
                    If Not cJMail.SendMSG Then
                        MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                        'adoConn.Execute "dir_insert_error '" & Date & "','" & "Error Sending Mail" & "','clsSendEMail',0"
                      Set adoConn = Nothing
                      Exit Sub
                    End If
                Else
                    'if the request is auto but its not in the list of workflow processes
                    'send a normal mail
                    Set cJMail = New clsJMail
                    
                    cJMail.EMailServer = "trend1.liberty.co.za"
                    cJMail.MsgFrom = "info@keytax.co.za"
                    cJMail.MSGFromName = "KeyTax: Tax Directives"
                    cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
                    cJMail.MSGBody = sEMailBody
                    cJMail.AddRecipient adoRs.Fields("EMail_address"), adorsEMail.Fields("requested_by")
                    strEmailAddress = adoRs.Fields("EMail_address") & " " & adorsEMail.Fields("requested_by")
                    
                    If sFileName <> "" Then
                      cJMail.AddAttachment sFileName
                    End If
                        
                    If Not cJMail.SendMSG Then
                        MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                        'adoConn.Execute "dir_insert_error '" & Date & "','" & "Error Sending Mail" & "','clsSendEMail',0"
                      Set adoConn = Nothing
                      Exit Sub
                    End If
                End If
            End If
        Else
'*******************************************************************
'NOT AN INTERFACE REQUEST - EMAIL TO USER
'*******************************************************************
            'Send Mail if its not an auto response
            iCounter = iCounter + 1
            Form2.List1.AddItem CStr(iCounter) & " - " & adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
            Form2.List1.ListIndex = iCounter - 1
            Form2.List1.Refresh
            
            Set cJMail = New clsJMail
            
            cJMail.EMailServer = "trend1.liberty.co.za"
            cJMail.MsgFrom = "info@keytax.co.za"
            cJMail.MSGFromName = "KeyTax: Tax Directives"
            cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
            cJMail.MSGBody = sEMailBody
            cJMail.AddRecipient adoRs.Fields("EMail_address"), adorsEMail.Fields("requested_by")
            strEmailAddress = adoRs.Fields("EMail_address") & " " & adorsEMail.Fields("requested_by")
            
            If sFileName <> "" Then
              cJMail.AddAttachment sFileName
            End If
            
            If Not cJMail.SendMSG Then
                MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                Set adoConn = Nothing
                Exit Sub
            End If
'*******************************************************************
'EMAIL QA COPY OF EMAIL TO SHANE,RICHARD,TINA
'*******************************************************************
            'Send QA Copy
            If iCounter = 1 Then
                Set cJMail = New clsJMail
                cJMail.EMailServer = "trend1.liberty.co.za"
                cJMail.MsgFrom = "info@keytax.co.za"
                cJMail.MSGFromName = "KeyTax Quality Assurance"
                cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
                cJMail.MSGBody = sEMailBody
                cJMail.AddRecipient "richard@mindkey.co.za", "Richard Knowles"
                'cJMail.AddRecipient "shane@mindkey.co.za", "Shane Smith"
                cJMail.AddRecipient "tina@mindkey.co.za", "Tina Vorster"
                cJMail.AddRecipient "Johan@mindkey.co.za", "Johan Conradie"
                cJMail.AddRecipient "sibusiso.maswanganye@liberty.co.za", "Brian Maswanganye"
                If sFileName <> "" Then
                    cJMail.AddAttachment sFileName
                End If
                'send the email to the relevant recipient
                If Not cJMail.SendMSG Then
                    MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                    Set adoConn = Nothing
                    Exit Sub
                End If
            End If
        End If
    Else
        'send the email to the normal recipient
'*******************************************************************
'NOT AN INTERFACE REQUEST - EMAIL TO USER
'*******************************************************************
        iCounter = iCounter + 1
        Form2.List1.AddItem CStr(iCounter) & " - " & adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
        Form2.List1.ListIndex = iCounter - 1
        Form2.List1.Refresh
        
        Set cJMail = New clsJMail
            
        cJMail.EMailServer = "trend1.liberty.co.za"
        cJMail.MsgFrom = "info@keytax.co.za"
        cJMail.MSGFromName = "KeyTax: Tax Directives"
        cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
        cJMail.MSGBody = sEMailBody
        cJMail.AddRecipient adoRs.Fields("EMail_address"), adorsEMail.Fields("requested_by")
        strEmailAddress = adoRs.Fields("EMail_address") & " " & adorsEMail.Fields("requested_by")
        
        If sFileName <> "" Then
          cJMail.AddAttachment sFileName
        End If
            
        If Not cJMail.SendMSG Then
            MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
          Set adoConn = Nothing
          Exit Sub
        End If
'*******************************************************************
'EMAIL QA COPY OF EMAIL TO SHANE,RICHARD,TINA
'*******************************************************************
        'Send QA Copy
        If iCounter = 1 Then
            Set cJMail = New clsJMail
            cJMail.EMailServer = "trend1.liberty.co.za"
            cJMail.MsgFrom = "info@keytax.co.za"
            cJMail.MSGFromName = "KeyTax Quality Assurance"
            cJMail.MSGSubject = adorsEMail.Fields("Taxpayer") & ": Directive requested on " & Format(adorsEMail.Fields("request_date"), "YYYYMMDD")
            cJMail.MSGBody = sEMailBody
            cJMail.AddRecipient "richard@mindkey.co.za", "Richard Knowles"
            'cJMail.AddRecipient "shane@mindkey.co.za", "Shane Smith"
            cJMail.AddRecipient "tina@mindkey.co.za", "Tina Vorster"
            cJMail.AddRecipient "Johan@mindkey.co.za", "Johan Conradie"
            cJMail.AddRecipient "sibusiso.maswanganye@liberty.co.za", "Brian Maswanganye"
            If sFileName <> "" Then
                cJMail.AddAttachment sFileName
            End If
            'send the email to the relevant recipient
            If Not cJMail.SendMSG Then
                MsgBox "Error Sending Mail. Req Seq Num: " & adoRs.Fields("req_seq_num")
                Set adoConn = Nothing
                Exit Sub
            End If
        End If
    End If
    '************************************************************************
    '****       END ANTHONY PATTONS CHANGES
    '************************************************************************
'Change email status to 'Y'
sStage = "Flagging as EMailed " & adoRs.Fields("req_seq_num")
adoConn.Execute "Dir_Update_Emailed_YN " & adoRs.Fields("req_seq_num") & ", 'Y'" 'mark as EMailed
                
adorsEMail.Close
adoRs.MoveNext 'move to next Directive to process

Wend 'no more directives to process

Set cJMail = Nothing

'Clean Up
crFormC.DiscardSavedData 'clear out report data
'release Recordset of all EMailing Details
Set adorsEMail = Nothing
'release Recordset of all Pending Directives
adoRs.Close
Set adoRs = Nothing
'close and release connection to SQL Server
adoConn.Close
Set adoConn = Nothing

'Shane: Only for the purpose of testing
sPDFPath = "C:\"
Set objReg = Nothing
If Dir(sPDFPath & "*.pdf") <> "" Then
   Kill sPDFPath & "*.pdf"
End If


'MsgBox "Complete"
Unload Form2

Exit Sub

Send_FormC_Error:

If Err <> 13 And Err <> 94 Then
  sStage = sStage & " " & Err.Description
  While InStr(sStage, "'") > 0
    sStage = Left(sStage, InStr(sStage, "'") - 1) & "`" & Mid$(sStage, InStr(sStage, "'") + 1)
  Wend
  'adoConn.Execute "dir_insert_error '" & Date & "','" & sStage & "','clsDirSendFormC'," & Err.Number
  MsgBox Err.Number & Err.Description
  Set adoConn = Nothing
  Exit Sub
End If

Resume Next

End Sub

Private Sub SendErrorEmail(lngReqSeqNum As Long, strErrMsg As String)

    Set cJMail = New clsJMail
    cJMail.EMailServer = "trend1.liberty.co.za"
    cJMail.MsgFrom = "email@keytax.co.za"
    cJMail.MSGFromName = "Email Automation"
    cJMail.MSGSubject = "Email Error"
    cJMail.MSGBody = strErrMsg
    'cJMail.AddRecipient "shane@mindkey.co.za", "Shane Smith"
    cJMail.AddRecipient "tina@mindkey.co.za", "Tina Vorster"
    cJMail.AddRecipient "Johan@mindkey.co.za", "Johan Conradie"
    cJMail.AddRecipient "sibusiso.maswanganye@liberty.co.za", "Brian Maswanganye"
    If Not cJMail.SendMSG Then
        MsgBox "Unbale to send error email for ReqSeqNum: " & lngReqSeqNum
        Exit Sub
    End If

End Sub
Public Function IsAppRunning() As Boolean
    Dim blnResult As Boolean
    Dim strOldTitle As String

    'is the application already loaded?
    'if so then restore the previous instance of the prog
    'by making it active and bringing it to the top

    If App.PrevInstance Then
        'change the new instance title to prevent it from being located instead of the original
        strOldTitle = App.Title
        App.Title = App.Title & "...Duplicate"
        RestorePreviousInstance strOldTitle
        App.Title = strOldTitle

        blnResult = True
    Else
        blnResult = False
    End If

    IsAppRunning = blnResult

End Function


Private Sub RestorePreviousInstance(ByVal strPreviousTitle As String)
    Dim hPrevWin As Long
    Dim lngRetVal As Long
    Dim apiWindowPlacement As TWINDOWPLACEMENT

    'using the class name for the compiled exe class
    'prevents the routine from finding and attempting to
    'activate the project form of the same name

    hPrevWin = FindWindow(VB5_CLASS_NAME, strPreviousTitle)

      DoEvents

      If hPrevWin > 0 Then
        ' Get the current window state of the previous instance
        apiWindowPlacement.lngLength = Len(apiWindowPlacement)
        lngRetVal = GetWindowPlacement(hPrevWin, apiWindowPlacement)

        ' If the ShowCmd member indicates that the window
        ' is currently minimized, it needs to be restored, so...
        If apiWindowPlacement.lngShowCmd = SW_SHOWMINIMIZED Then
          apiWindowPlacement.lngShowCmd = Len(apiWindowPlacement)
          apiWindowPlacement.lngFlags = 0&
          apiWindowPlacement.lngShowCmd = SW_SHOWNORMAL
          lngRetVal = SetWindowPlacement(hPrevWin, apiWindowPlacement)
        End If

        ' Bring the window to the front and make it the active
        ' window.  Without this, it may remain behind other windows
        lngRetVal = SetForegroundWindow(hPrevWin)

        DoEvents

      End If

End Sub



